package serverSide;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author endlesslove
 */
public class ConnectUnit {

    /**
     * @return the _connectionReport
     */
    public static Connection getConnectionReport() {
        return _connectionReport;
    }

    /**
     * @param aConnectionReport the _connectionReport to set
     */
    public static void setConnectionReport(Connection aConnectionReport) {
        _connectionReport = aConnectionReport;
    }

    //Biáº¿n káº¿t ná»‘i cÆ¡ báº£n
    public MSSQLConnect _connect;
    private static Connection _connectionReport;
    // HÃ m há»— trá»£ Select CSDL
    // SELECT * FORM TableName WHERE Condition ORDER BY OrderBy;
    public ResultSet Select(String TableName, String Condition,String OrderBy) throws Exception
    {
        // Khai bÃ¡o biáº¿n StringBuilder Ä‘á»ƒ táº¡o chuá»—i Select.
        StringBuilder query = new StringBuilder( "SELECT * FROM " + TableName);
        // Ä�Æ°a cÃ¢u lá»‡nh Ä‘iá»�u kiá»‡n vÃ o trong cÃ¢u query
        this.AddCondition(query, Condition);
        // Ä�Æ°a cÃ¢u lá»‡nh Order vÃ o cÃ¢u query
        this.AddOrderBy(query, OrderBy);
        // ChÃ¨n kÃ½ tá»± ';' vÃ o cuá»‘i dÃ²ng lá»‡nh cá»§a Ä‘á»ƒ cÃ¡ch cÃ¡c cÃ¢u lá»‡nh.
        query.append(";");
        // Thá»±c thi cÃ¢u query vÃ  tráº£ káº¿t quáº£ ra ngoÃ i
        return _connect.excuteQuery(query.toString());
    }

    // HÃ m over load Select giáº£m OdrderBy parameter
    public ResultSet Select(String TableName, String Condition) throws Exception
    {
        return this.Select(TableName, Condition, null);
    }
    
    // HÃ m over load Select giáº£m Condition paramter
     public ResultSet Select(String TableName) throws Exception
     {
        return this.Select(TableName, null);
    }

     // HÃ m thÃªm Ä‘iá»�u kiá»‡n vÃ o query
     private void AddCondition(StringBuilder query, String Condition)
     {
         // Kiá»ƒm tra náº¿u condiotn khÃ¡c null
         if(Condition!=null)
            query.append(" WHERE ").append(Condition);
    }

     // HÃ m thÃªm OrderBy vÃ o query
    private void AddOrderBy(StringBuilder query,String OrderBy)
    {
        // Kiá»ƒm tra OrderBy khÃ¡c null
        if(OrderBy!=null)
            query.append(" ORDER BY ").append(OrderBy);
    }

    // HÃ m há»— trá»£ Insert xuá»‘ng CSDL.
    // INSERT INTO TableName (columnName...) VALUES (column Values...);
    public boolean Insert(String TableName, HashMap<String,Object> ColumnValues) throws Exception
    {
        // Khai bÃ¡o biáº¿n StringBuilder Ä‘á»ƒ táº¡o chuá»—i Select.
        StringBuilder query =  new StringBuilder("INSERT INTO " + TableName);
        // Khai bÃ¡o biáº¿n StringBuilder Ä‘á»ƒ táº¡o chuá»—i Column Values.
        StringBuilder valueInsert = new StringBuilder();

        query.append("(");
        // Duyá»‡t vÃ  Ä‘Æ°a thÃ´ng tin tÃªn cá»™t vÃ  giÃ¡ trá»‹ cá»§a cá»™t vÃ o cÃ¢u query
        for(String key : ColumnValues.keySet())
        {
            query.append(key).append(",");
            valueInsert.append("'").append(ColumnValues.get(key).toString()).append("' ,");
        }
        // Cáº¯t bá»›t kÃ½ tá»± ',' cuá»‘i cÃ¢u
        query = query.delete(query.length()-1, query.length());
        valueInsert = valueInsert.delete(valueInsert.length()-1, valueInsert.length());

        // Ä�Æ°a giÃ¡ trá»‹ cá»§a cá»™t vÃ o cÃ¢u query
        query.append(") VALUES(").append(valueInsert.toString()).append(")");
        // ChÃ¨n kÃ½ tá»± ';' vÃ o cuá»‘i dÃ²ng lá»‡nh cá»§a Ä‘á»ƒ cÃ¡ch cÃ¡c cÃ¢u lá»‡nh.
        query.append(";");
        // Thá»±c thi cÃ¢u query vÃ  tráº£ káº¿t quáº£ ra ngoÃ i
        return this._connect.executeUpdate(query.toString()) > 0;
    }

    // HÃ m há»— trá»£ Update CSDL
    // UPDATE TableName SET ColumnName = ColumnValue WHERE Condiotion;
    public boolean Update(String TableName, HashMap<String,Object> ColumnValues, String Condition) throws Exception 
    {
        // Khai bÃ¡o biáº¿n StringBuilder Ä‘á»ƒ táº¡o chuá»—i Select.
        StringBuilder query = new StringBuilder("UPDATE " + TableName + " SET ");

        // Duyá»‡t vÃ  Ä‘Æ°a thÃ´ng tin tÃªn cá»™t vÃ  giÃ¡ trá»‹ cá»§a cá»™t vÃ o cÃ¢u query
        for(String key : ColumnValues.keySet())
        {
            query.append(key).append(" = '").append(ColumnValues.get(key).toString()).append("',");
        }
        // Cáº¯t bá»›t kÃ½ tá»± ',' cuá»‘i cÃ¢u
        query = query.delete(query.length()-1, query.length());
        // Ä�Æ°a cÃ¢u lá»‡nh Ä‘iá»�u kiá»‡n vÃ o trong cÃ¢u query
        this.AddCondition(query, Condition);
        // ChÃ¨n kÃ½ tá»± ';' vÃ o cuá»‘i dÃ²ng lá»‡nh cá»§a Ä‘á»ƒ cÃ¡ch cÃ¡c cÃ¢u lá»‡nh.
        query.append(";");
        // Thá»±c thi cÃ¢u query vÃ  tráº£ káº¿t quáº£ ra ngoÃ i
        return this._connect.executeUpdate(query.toString())> 0 ;
    }

    // HÃ m há»— trá»£ DELETE trong CSDL
    // DELETE FROM TableName WHERE Condition.
    public boolean Delete(String TableName,String Condition) throws Exception
    {
        // Khai bÃ¡o biáº¿n StringBuilder Ä‘á»ƒ táº¡o chuá»—i Select.
        StringBuilder query = new StringBuilder( "DELETE FROM " + TableName);
        // Ä�Æ°a cÃ¢u lá»‡nh Ä‘iá»�u kiá»‡n vÃ o trong cÃ¢u query
        this.AddCondition(query, Condition);
        // ChÃ¨n kÃ½ tá»± ';' vÃ o cuá»‘i dÃ²ng lá»‡nh cá»§a Ä‘á»ƒ cÃ¡ch cÃ¡c cÃ¢u lá»‡nh.
        query.append(";");
        // Thá»±c thi cÃ¢u query vÃ  tráº£ káº¿t quáº£ ra ngoÃ i
        return this._connect.executeUpdate(query.toString()) > 0;
    }

    // Ä�áº¿m sá»‘ cá»™t trong Result select tá»« CSDL.
    public static int getColumnCount(ResultSet result) throws SQLException
    {
        // Láº¥t sá»‘ lÆ°á»£ng cá»£t tá»« MetaData cá»§a Result.
        return result.getMetaData().getColumnCount();
    }

    // HÃ m láº¥y danh sÃ¡ch tÃªn cá»™t trong Result select tá»« CSDL.
    public static String[]getColumnName(ResultSet result) throws SQLException
    {
        // Láº¥y ResultSetMetaDate tá»« Result
        ResultSetMetaData rsMetaData = (ResultSetMetaData)result.getMetaData();
        // Láº¥y sá»‘ lÆ°á»£ng cá»™t trong Result.
        int ColumnCount = rsMetaData.getColumnCount();
        // Khai bÃ¡o danh sÃ¡ch cÃ¡c cá»™t.
        String[] list = new String[ColumnCount];
        // Duyá»‡t cÃ¡c cá»™t
        for(int i=0;i<ColumnCount;i++)
            // Láº¥y ten Ä‘Æ°a vÃ o danh sÃ¡ch
            list[i]=rsMetaData.getColumnName(i);
        // Tráº£ danh sÃ¡ch ra ngoÃ i.
        return list;
    }

    // Ä�Ã³ng káº¿t ná»‘i.
    public void Close() throws SQLException
    {
        this._connect.Close();}

    // HÃ m khá»Ÿi táº¡o vá»›i cÃ¡c thÃ´ng sá»‘ káº¿t ná»‘i DataBase Server.
    public ConnectUnit(String host,int Port, String user,String pass,String db) 
    {
            this._connect = new MSSQLConnect(host,Port, user,pass,db);
    }

    public boolean Connect()
    {
        try 
        {
            setConnectionReport(_connect.getConnect());
            return true;
        } 
        catch (Exception ex) 
        {
            Logger.getLogger(ConnectUnit.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }
    public ResultSet Excute(String q) throws Exception
    {
    	return _connect.excuteQuery(q);
    }
    
   
}
